package dbtools;

/**
 * Title: 数据库查询结果集操作类
 * 
 */

import dbtools.GlobalParam;

import java.sql.*;
import java.util.ArrayList;
import java.util.Properties;


public class DBQuery {

	/***
	 * 得到连接
	 *
	 * */
	public static synchronized  final Connection getConnect(String dbname) {

		String sjkurl = GlobalParam.url; //url
		String sjkuser = GlobalParam.username; //数据库用户
		String sjkpwd = GlobalParam.password; //数据库口令


/*
		Properties p = new Properties();
		String fileName="src/resource/conf/dataSource.properties";*/
		try {
/*			InputStream in = new FileInputStream(new File(fileName));
			p.load(in);
			in.close();*/
			//这里有人用new FileInputStream(fileName),不过这种方式找不到配置文件。有人说是在classes下，我调过了，不行。

/*			String driver = p.getProperty("jdbc.driver");
			String url = p.getProperty("jdbc.url");
			String user = p.getProperty("jdbc.username");
			String password =p.getProperty("jdbc.password");*/

			String driver = GlobalParam.driver;
			String url = GlobalParam.url;
			if (dbname.equals("mysqltx")){
				url = GlobalParam.urlx;
			}
			String user = GlobalParam.username;
			String password =GlobalParam.password;

			Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");
			Properties info = new Properties();
			info.setProperty("proxool.maximum-connection-count", "100");
			info.setProperty("proxool.house-keeping-test-sql",
					"select now()");
			info.setProperty("user", user);
			info.setProperty("password", password);
			url += "?relaxAutoCommit=true&zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf-8";

			Connection con = DriverManager.getConnection("proxool.example:"
					+ driver + ":" + url, info);


	/*		Class.forName("com.mysql.jdbc.Driver");
			Connection con = java.sql.DriverManager.getConnection(url,
					user, password);*/

			System.out.println(con);
			return con;

		} catch (Throwable t) {
			t.printStackTrace();
			return null;
		} finally {
		}
	}






	/**
	 * 取得查询结果集
	 * @param strSql String  查询SQL语句(select ... from ....)
	 * @param con Connection 数据库联结
	 * @param innum int      结果集内最大记录数
	 * @return ArrayList     结果集
	 *                        结果集格式： String[] 0--字段名  1...n 记录值
	 */
	private static final ArrayList makeQuery(
		String strSql,
		Connection con,
		int innum) {

		strSql = SqlTools.standquerysql(strSql, "");

		Statement stmtDB;
		ResultSet rsetTemp;
		int nCode;
		ArrayList vectStore = new ArrayList();
		String tableName = "";
		try {
			stmtDB = con.createStatement();

			String strTemp = strSql.toLowerCase().trim();
			rsetTemp = stmtDB.executeQuery(strSql.trim());

			vectStore = makeValue(rsetTemp, innum);

			if (rsetTemp != null)
				rsetTemp.close();
			stmtDB.close();
			stmtDB = null;
			rsetTemp = null;
			return vectStore;
		} catch (SQLException e) {
			e.printStackTrace();
			stmtDB = null;
			rsetTemp = null;
			return vectStore;
		} finally {
			stmtDB = null;
			rsetTemp = null;
		}
	}
	/**
	 * 取得查询结果集
	 * @param :  ResultSet
	 * @param :  int 记录数
	 * @return : ArrayList
	 */
	private static final ArrayList makeValue(ResultSet rsetTemp, int innum) {
		Statement stmtDB;
		String strValue = "";
		String strColName = "";
		int nRows = 0;
		int nCols;
		int nType;
		ArrayList vectStore = new ArrayList();
		ResultSetMetaData metaTemp;

		String nowdb = "mysql";


		try {
			metaTemp = rsetTemp.getMetaData();
			nCols = metaTemp.getColumnCount();
			String[] tmpstr = new String[nCols];
			if (nowdb.toLowerCase().startsWith("mysql")) {
				for (int i = 0; i < nCols; i++) {
					tmpstr[i] = metaTemp.getColumnLabel(i + 1);
				}
			} else {
				for (int i = 0; i < nCols; i++) {
					tmpstr[i] = metaTemp.getColumnName(i + 1);
				}
			}
			vectStore.add(tmpstr);

			String strTypeName;
			int totrow = 0;

			while (rsetTemp.next()) {
				if (innum > 0 && totrow >= innum) {
					break;
				}
				tmpstr = new String[nCols];
				for (int i = 0; i < nCols; i++) {
					nType = metaTemp.getColumnType(i + 1);
					strTypeName = metaTemp.getColumnTypeName(i + 1);

					tmpstr[i] = "";

					try {

						switch (nType) {
							case -1:
								if (nowdb.toLowerCase().startsWith("mysql")) {
									tmpstr[i] = rsetTemp.getString(metaTemp
											.getColumnLabel(i + 1));
								} else {
									tmpstr[i] = rsetTemp.getString(metaTemp
											.getColumnName(i + 1));
								}
								break;
							case -4:
								if (nowdb.toLowerCase().startsWith("mysql")) {
									if (rsetTemp.getBlob(metaTemp
											.getColumnLabel(i + 1)) == null) {
										tmpstr[i] = "";
									} else {
										tmpstr[i] = rsetTemp.getBlob(
												metaTemp.getColumnLabel(i + 1))
												.toString().trim();
									}
								} else {
									if (rsetTemp.getBlob(metaTemp
											.getColumnName(i + 1)) == null) {
										tmpstr[i] = "";
									} else {
										tmpstr[i] = rsetTemp.getBlob(
												metaTemp.getColumnName(i + 1))
												.toString().trim();
									}
								}
								break;
							case -5:
								if (nowdb.toLowerCase().startsWith("mysql")) {
									tmpstr[i] = String.valueOf(rsetTemp
											.getString(metaTemp
													.getColumnLabel(i + 1)));
								} else {
									tmpstr[i] = String.valueOf(rsetTemp
											.getString(metaTemp
													.getColumnName(i + 1)));
								}
								break;
							case -6:
								if (nowdb.toLowerCase().startsWith("mysql")) {
									tmpstr[i] = String
											.valueOf(rsetTemp.getInt(metaTemp
													.getColumnLabel(i + 1)));
								} else {
									tmpstr[i] = String.valueOf(rsetTemp
											.getInt(metaTemp.getColumnName(i + 1)));

								}
								break;
							case 1:
								if (nowdb.toLowerCase().startsWith("mysql")) {
									tmpstr[i] = rsetTemp.getString(metaTemp
											.getColumnLabel(i + 1));
								} else {
									tmpstr[i] = rsetTemp.getString(metaTemp
											.getColumnName(i + 1));

								}
								break;
							case 12:
								if (nowdb.toLowerCase().startsWith("mysql")) {
									if (rsetTemp.getString(metaTemp
											.getColumnLabel(i + 1)) == null) {
										tmpstr[i] = "";
									} else {
										tmpstr[i] = rsetTemp.getString(metaTemp
												.getColumnLabel(i + 1));
									}
								} else {
									if (rsetTemp.getString(metaTemp
											.getColumnName(i + 1)) == null) {
										tmpstr[i] = "";
									} else {
										tmpstr[i] = rsetTemp.getString(metaTemp
												.getColumnName(i + 1));
									}
								}
								break;
							case 2:
								String values = "";
								if (nowdb.toLowerCase().startsWith("mysql")) {
									values = rsetTemp.getString(metaTemp
											.getColumnLabel(i + 1));
								} else {
									values = rsetTemp.getString(metaTemp
											.getColumnName(i + 1));

								}
								if (values == null) {
									values = "";
								} else {
									if (values.toUpperCase().equals("NULL"))
										values = "";
								}
								tmpstr[i] = values;
								if (tmpstr[i].startsWith("."))
									tmpstr[i] = "0" + tmpstr[i];
								break;
							case 3:
								if (nowdb.toLowerCase().startsWith("mysql")) {
									if (rsetTemp.getString(metaTemp
											.getColumnLabel(i + 1)) == null)
										tmpstr[i] = "";
									else
										tmpstr[i] = rsetTemp.getString(
												metaTemp.getColumnLabel(i + 1))
												.trim();
								} else {
									if (rsetTemp.getString(metaTemp
											.getColumnName(i + 1)) == null)
										tmpstr[i] = "";
									else
										tmpstr[i] = rsetTemp.getString(
												metaTemp.getColumnName(i + 1))
												.trim();

								}
								break;
							case 4:
								String value = "";
								if (nowdb.toLowerCase().startsWith("mysql")) {
									value = rsetTemp.getString(metaTemp
											.getColumnLabel(i + 1));
								} else {
									value = rsetTemp.getString(metaTemp
											.getColumnName(i + 1));

								}
								if (value == null) {
									value = "";
								} else {
									if (value.toUpperCase().equals("NULL"))
										value = "";
								}
								tmpstr[i] = value;
								break;
							case 5:
								if (nowdb.toLowerCase().startsWith("mysql")) {
									tmpstr[i] = String
											.valueOf(rsetTemp.getInt(metaTemp
													.getColumnLabel(i + 1)));
								} else {
									tmpstr[i] = String.valueOf(rsetTemp
											.getInt(metaTemp.getColumnName(i + 1)));

								}
								break;
							case 7:
								if (nowdb.toLowerCase().startsWith("mysql")) {
									if (rsetTemp.getString(metaTemp
											.getColumnLabel(i + 1)) == null)
										tmpstr[i] = "";
									else
										tmpstr[i] = rsetTemp.getString(
												metaTemp.getColumnLabel(i + 1))
												.trim();
								} else {
									if (rsetTemp.getString(metaTemp
											.getColumnName(i + 1)) == null)
										tmpstr[i] = "";
									else
										tmpstr[i] = rsetTemp.getString(
												metaTemp.getColumnName(i + 1))
												.trim();

								}
								break;
							case 8:
								if (nowdb.toLowerCase().startsWith("mysql")) {
									if (rsetTemp.getString(metaTemp
											.getColumnLabel(i + 1)) == null)
										tmpstr[i] = "";
									else
										tmpstr[i] = rsetTemp.getString(
												metaTemp.getColumnLabel(i + 1))
												.trim();
								} else {
									if (rsetTemp.getString(metaTemp
											.getColumnName(i + 1)) == null)
										tmpstr[i] = "";
									else
										tmpstr[i] = rsetTemp.getString(
												metaTemp.getColumnName(i + 1))
												.trim();

								}
								break;
							case 91:
								String temp = "";
								if (nowdb.toLowerCase().startsWith("mysql")) {

									temp = metaTemp.getColumnLabel(i + 1);

									if (rsetTemp.getString(temp) != null) {
										tmpstr[i] = rsetTemp.getDate(
												metaTemp.getColumnLabel(i + 1))
												.toString();
									}
								} else {
									temp = metaTemp.getColumnName(i + 1);

									if (rsetTemp.getString(temp) != null) {
										tmpstr[i] = rsetTemp.getDate(
												metaTemp.getColumnName(i + 1))
												.toString();
									}
								}
								break;
							case 92:
								if (nowdb.toLowerCase().startsWith("mysql")) {
									if (rsetTemp.getTime(metaTemp
											.getColumnLabel(i + 1)) != null) {
										tmpstr[i] = rsetTemp.getTime(
												metaTemp.getColumnLabel(i + 1))
												.toString();
									}
								} else {
									if (rsetTemp.getTime(metaTemp
											.getColumnName(i + 1)) != null) {
										tmpstr[i] = rsetTemp.getTime(
												metaTemp.getColumnName(i + 1))
												.toString();
									}

								}
								break;
							case 93:
								if (nowdb.toLowerCase().startsWith("mysql")) {
									if (rsetTemp.getTimestamp(metaTemp
											.getColumnLabel(i + 1)) != null) {

										tmpstr[i] = rsetTemp.getTimestamp(
												metaTemp.getColumnLabel(i + 1))
												.toString().trim();
									}
									if (tmpstr[i].length() > 19) {
										tmpstr[i] = tmpstr[i].substring(0, 19);
									}
								} else {
									if (rsetTemp.getString(metaTemp
											.getColumnName(i + 1)) != null) {
										tmpstr[i] = rsetTemp.getString(
												metaTemp.getColumnName(i + 1))
												.trim();
									}
								}

								break;
							case 2004:
								// if (nowdb.toLowerCase().startsWith("db2")) {
								// tmpstr[i] = StaticTools.getDbCLOBContent(
								// rsetTemp, i + 1);
								// }
								// if (nowdb.toLowerCase().startsWith("oracle")) {
								// tmpstr[i] = StaticTools.getCLOBContent(
								// rsetTemp, i + 1);
								// }
								tmpstr[i] = "";
								break;
							case 2005:
								tmpstr[i] = "";
/*								if (nowdb.toLowerCase().startsWith("db2")) {
									tmpstr[i] = StringUtil.getDbCLOBContentDb2(
											rsetTemp, i + 1);
								}
								if (nowdb.toLowerCase().startsWith("oracle")) {
									tmpstr[i] = StringUtil.getCLOBContent(
											rsetTemp, i + 1);
								}*/
								break;
							default:
								tmpstr[i] = "无法识别数据类型";
						}
					} catch (Exception e) {
						e.printStackTrace();
						return vectStore;
					}
					if (tmpstr[i] == null)
						tmpstr[i] = "";
					tmpstr[i] = tmpstr[i].trim();

				}
				vectStore.add(tmpstr);
				totrow++;
			}
			metaTemp = null;
			return vectStore;
		} catch (SQLException e) {
			e.printStackTrace();
			metaTemp = null;
			return new ArrayList();
		} finally {
			metaTemp = null;
		}
	}
	/**
	 * 取得查询结果集
	 * 最多返回2000条数据
	 *
	 * @param (String) str 查询语句
	 * @return (ArrayList) 结果集 ( null 未成功)
	 *
	 */
	public static final ArrayList makeQuery(String str) {
		return makeQuery(str, 2000);
	}
	/**
	 * 取得查询结果集
	 *
	 * @param (String) str 查询语句
	 * @param (int)    innum 需返回的最大数  小于 0  则全返回, 0 返回2000条 大于 0 返回指定的条数
	 * @return (ArrayList) 结果集 ( null 未成功)
	 *
	 */
	public static final ArrayList makeQuery(String str, int innum) {
 
		str = SqlTools.standquerysql(str, "");
		ArrayList vectResult = null;
		Connection con = null;

		try {

			con = getConnect("mysql");
			if (con == null)
				return null;

			if (innum == 0)
				innum = 2000;
			if (innum < -10) {
					str = str + " LIMIT 0,0";
			} else {
				if (innum < -1) {
					str = str + " LIMIT 0,2000";
				}
			}

			//if (debugsign.equals("yes")) {

			System.err.println(str);
			vectResult = makeQuery(str, con, innum);
			con.close();
			if (vectResult.size() > 0) {
				return vectResult;
			} else {
				return null;
			}
		} catch (Exception s) {
			try {
				con.close();
			} catch (Exception s2) {
				s2.printStackTrace();
				return null;
			}
			s.printStackTrace();
			vectResult = null;
			return null;
		} finally {
		}
	}
 
	private static final ArrayList makeDetaValue(
		String inssql,
		ResultSet rsetTemp,
		String dbname) {
		String strValue = "";
		String strColName = "";
		int nRows = 0;
		int nCols;
		int nType;
		ArrayList vectStore = new ArrayList();
		ResultSetMetaData metaTemp;
		try {
			metaTemp = rsetTemp.getMetaData();
			nCols = metaTemp.getColumnCount();
			for (int i = 0; i < nCols; i++) {
				String[] tmpstr = new String[8];
				tmpstr[0] = metaTemp.getColumnName(i + 1); //列名称
				nType = metaTemp.getColumnType(i + 1);
				try {
					switch (nType) {
						case -1 :
							tmpstr[2] = "string";
							break;
						case -4 :
							break;
						case -5 :
							tmpstr[2] = "number";
							break;
						case -6 :
							tmpstr[2] = "number";
							break;
						case 1 :
							tmpstr[2] = "string";
							break;
						case 12 :
							tmpstr[2] = "string";
							break;
						case 2 :
							tmpstr[2] = "number";
							break;
						case 3 :
							tmpstr[2] = "number";
							break;
						case 4 :
							tmpstr[2] = "number";
							break;
						case 5 :
							tmpstr[2] = "number";
							break;
						case 7 :
							tmpstr[2] = "string";
							break;
						case 8 :
							tmpstr[2] = "string";
							break;
						case 91 :
							tmpstr[2] = "date";
							break;
						case 92 :
							tmpstr[2] = "time";
							break;
						case 93 :
							tmpstr[2] = "datetime";
							break;
						case 2005 :
							tmpstr[2] = "string";
							break;
						default :
							tmpstr[2] = "error";
					}
				} catch (Exception e) {
					e.printStackTrace();
					return vectStore;
				}
				if (tmpstr[2].equals("number")) {
					tmpstr[1] = String.valueOf(metaTemp.getPrecision(i + 1));
					//整数位
				} else {
					tmpstr[1] =
						String.valueOf(metaTemp.getColumnDisplaySize(i + 1));
					//长度

					if (tmpstr[1].toLowerCase().equals("date")) {
						tmpstr[7] = "10";
					}
					if (tmpstr[1].toLowerCase().equals("datetime")) {
						tmpstr[7] = "20";
					}
				}
				tmpstr[3] = metaTemp.getColumnLabel(i + 1).trim(); //标题，注释
				tmpstr[4] = String.valueOf(metaTemp.isNullable(i + 1)); //是否可空
				tmpstr[5] = String.valueOf(metaTemp.getScale(i + 1)); //小数位
				tmpstr[6] = getTableName(inssql, tmpstr[3]);
				tmpstr[7] =
					String.valueOf(metaTemp.getColumnDisplaySize(i + 1));
				//显示宽度

				if (tmpstr[2].toLowerCase().equals("date")) {
					tmpstr[7] = "10";
				}
				if (tmpstr[2].toLowerCase().equals("datetime")) {
					tmpstr[7] = "20";
				}
				//字段长度
				vectStore.add(tmpstr);
			}
			metaTemp = null;
			return vectStore;
		} catch (Exception e) {
			e.printStackTrace();
			metaTemp = null;
			return vectStore;
		} finally {
			metaTemp = null;
		}
	}
	private static String getTableName(String inssql, String fieldname) {
		inssql = inssql.toUpperCase();
		inssql = "," + inssql.trim().substring(7);

		int nsql = 0;
		if (inssql.indexOf(" " + fieldname + ",") > 0) {
			inssql = inssql.substring(0, inssql.indexOf(" " + fieldname + ","));
			nsql = nsql + 1;
		}
		if (inssql.indexOf(" " + fieldname + " ") > 0) {
			inssql = inssql.substring(0, inssql.indexOf(" " + fieldname + " "));
			nsql = nsql + 1;
		}
		if (nsql > 0) {
			inssql = inssql.trim();
			if (inssql.endsWith(")")) {
				inssql = inssql.substring(inssql.lastIndexOf("(") + 1);
				if (inssql.startsWith("A."))
					return "a";
				if (inssql.startsWith("B."))
					return "b";
				if (inssql.startsWith("C."))
					return "c";
				if (inssql.startsWith("D."))
					return "d";
				if (inssql.startsWith("E."))
					return "e";
				if (inssql.startsWith("F."))
					return "f";
				if (inssql.startsWith("G."))
					return "g";
			} else {
				inssql = inssql.substring(inssql.lastIndexOf(",") + 1).trim();
				if (inssql.startsWith("A."))
					return "a";
				if (inssql.startsWith("B."))
					return "b";
				if (inssql.startsWith("C."))
					return "c";
				if (inssql.startsWith("D."))
					return "d";
				if (inssql.startsWith("E."))
					return "e";
				if (inssql.startsWith("F."))
					return "f";
				if (inssql.startsWith("G."))
					return "g";
			}
		} else {
			if (inssql.indexOf("A." + fieldname + ",") > 0)
				return "a";
			if (inssql.indexOf("A." + fieldname + " ") > 0)
				return "a";
		}
		return "";
	}
}
